﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Dirs;
using APPLICATION.ErrorDict;

namespace APPLICATION.Dict
{
    

    static class dataSet1
    {
        static public DataTable DtCity;
        static public DataTable Clients;

        static private ErrFinder ErrFinder1=new ErrFinder();

        #region Clients

        static public void FillClients()
        {
            GetConnectionString Sett = new GetConnectionString();

            DataAccess.TimsDS dataset = new DataAccess.TimsDS();
            Clients = dataset.Tables["Clients"];

            SqlConnection SqlConnection1 = new SqlConnection(Sett.ConnectionSrting());
            SqlCommand Sqlcmd1 = new SqlCommand("SELECT * FROM dbo.Clients", SqlConnection1);
            SqlDataAdapter SqlDA1 = new SqlDataAdapter(Sqlcmd1);
            
            try
            {
                SqlDA1.Fill(Clients);
                Clients.AcceptChanges();
            }
            catch (SqlException e1)
            {
                string ErrMsg = ErrFinder1.GetErrMessage(e1.GetType().Name, e1.ErrorCode.ToString(),e1.Message);
                System.Windows.Forms.MessageBox.Show(ErrMsg, "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
            }
        }

        static public void InsertDataClient(SqlCommand SqlCmd)
        {
            GetConnectionString Sett = new GetConnectionString();

            SqlConnection SqlConnection1 = new SqlConnection(Sett.ConnectionSrting());
            //SqlCmd = new SqlCommand();
            SqlCmd.Connection = SqlConnection1;
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlCmd.CommandText = "tsp_Clients";            

            try
            {
                SqlConnection1.Open();
                SqlCmd.ExecuteNonQuery();
                InsOrUpddataTable(ref Clients, SqlCmd.Parameters["ClientsID"] );
            }
            catch (SqlException e1)
            {
                string ErrMsg = ErrFinder1.GetErrMessage(e1.GetType().Name, e1.ErrorCode.ToString(), e1.Message);
                System.Windows.Forms.MessageBox.Show(ErrMsg, "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
            }
            finally
            {
                SqlConnection1.Close();
            }

        }

        static public void UpdateDataClient(SqlCommand SqlCmd)
        {
            GetConnectionString Sett = new GetConnectionString();

            SqlConnection SqlConnection1 = new SqlConnection(Sett.ConnectionSrting());
            //SqlCmd = new SqlCommand();
            SqlCmd.Connection = SqlConnection1;
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlCmd.CommandText = "tsp_Clients";

            try
            {
                SqlConnection1.Open();
                SqlCmd.ExecuteNonQuery();
                InsOrUpddataTable(ref Clients, SqlCmd.Parameters["ClientsID"]);
            }
            catch (SqlException e1)
            {
                string ErrMsg = ErrFinder1.GetErrMessage(e1.GetType().Name, e1.ErrorCode.ToString(), e1.Message);
                System.Windows.Forms.MessageBox.Show(ErrMsg, "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
            }
            finally
            {
                SqlConnection1.Close();
            }
        }

        static public void DeleteDataClient(int ClientsID)
        {
            GetConnectionString Sett = new GetConnectionString();
            SqlConnection SqlConnection1 = new SqlConnection(Sett.ConnectionSrting());

            SqlCommand Sqlcmd1 = new SqlCommand();
            Sqlcmd1.Connection = SqlConnection1;
            Sqlcmd1.CommandType = CommandType.StoredProcedure;
            Sqlcmd1.CommandText = "tsp_Clients";

            Sqlcmd1.Parameters.Add("ClientsID", SqlDbType.Int).Value = ClientsID;
            Sqlcmd1.Parameters.Add("Action", SqlDbType.TinyInt).Value = 2;
            try
            {
                SqlConnection1.Open();
                Sqlcmd1.ExecuteNonQuery();
                DeleteDataInDataTable(ref Clients, ClientsID);
            }
            catch (SqlException e1)
            {
                string ErrMsg = ErrFinder1.GetErrMessage(e1.GetType().Name, e1.Errors[0].Number.ToString(), e1.Message);
                System.Windows.Forms.MessageBox.Show(ErrMsg, "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);

            }
            finally
            {
                SqlConnection1.Close();
            }
        }


        #endregion


        #region Country

        static public void FillDtCity()
        {
            
            //Dirs.Settings1 Sett = new Dirs.Settings1();
            GetConnectionString Sett = new GetConnectionString();

            DataAccess.TimsDS dataset = new DataAccess.TimsDS();
            DtCity=dataset.Tables["dtCity"];
            
            SqlConnection SqlConnection1=new SqlConnection(Sett.ConnectionSrting());
            SqlCommand Sqlcmd1 = new SqlCommand("SELECT * FROM dbo.dtCity",SqlConnection1);
            SqlDataAdapter SqlDA1 = new SqlDataAdapter(Sqlcmd1);     
            
            try
            {
                SqlDA1.Fill(DtCity);
                DtCity.AcceptChanges();
            }
            catch (SqlException e1)
            {
                string ErrMsg = ErrFinder1.GetErrMessage(e1.GetType().Name, e1.ErrorCode.ToString(), e1.Message);
                System.Windows.Forms.MessageBox.Show(ErrMsg, "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
            }
        }               

        static public bool InsertDataCity(string hName, int mgrid)
        {           
            
            GetConnectionString Sett = new GetConnectionString();
            DAL.SqlExeptionMessageClass exeptionMessages = new DAL.SqlExeptionMessageClass(); // ExeptionMessages Class  From DAL

            SqlConnection SqlConnection1 = new SqlConnection(Sett.ConnectionSrting());
            SqlCommand Sqlcmd1 = new SqlCommand();
            Sqlcmd1.Connection = SqlConnection1;
            Sqlcmd1.CommandType = CommandType.StoredProcedure;
            Sqlcmd1.CommandText = "speIns_dtCity";

            SqlParameter OutPutPar = new SqlParameter("CodeID", SqlDbType.Int);
            OutPutPar.Direction = ParameterDirection.Output;

            Sqlcmd1.Parameters.Add(OutPutPar);
            Sqlcmd1.Parameters.Add("hName", SqlDbType.NVarChar).Value = hName;
            if (mgrid != 0)
            {
                Sqlcmd1.Parameters.Add("mgrid", SqlDbType.Int).Value = mgrid;
            }
            else
            {
                Sqlcmd1.Parameters.Add("mgrid", SqlDbType.Int).Value = DBNull.Value;
            }
            try
            {
                SqlConnection1.Open();
                Sqlcmd1.ExecuteNonQuery();
                InsOrUpddataTable(ref DtCity, OutPutPar);
                return true;
            }
            catch (SqlException e1)
            {
                string ErrMsg = ErrFinder1.GetErrMessage(e1.GetType().Name, e1.ErrorCode.ToString(), e1.Message);
                System.Windows.Forms.MessageBox.Show(ErrMsg, "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                return false ;
            }
            finally
            {
                SqlConnection1.Close();
            }
        }      

        static public bool UpdateDataCity(int CodeID, string hName, int mgrid)
        {
            GetConnectionString Sett = new GetConnectionString();
            DAL.SqlExeptionMessageClass exeptionMessages = new DAL.SqlExeptionMessageClass(); // ExeptionMessages Class  From DAL

            SqlConnection SqlConnection1 = new SqlConnection(Sett.ConnectionSrting());
            SqlCommand Sqlcmd1 = new SqlCommand();
            Sqlcmd1.Connection = SqlConnection1;
            Sqlcmd1.CommandType = CommandType.StoredProcedure;
            Sqlcmd1.CommandText = "speUpd_dtCity";

            SqlParameter OutPutParCodeID = new SqlParameter("CodeID", SqlDbType.Int);
            OutPutParCodeID.Direction = ParameterDirection.InputOutput;
            OutPutParCodeID.Value=CodeID;


            SqlParameter OutPutParhName = new SqlParameter("hName", SqlDbType.NVarChar);
            OutPutParhName.Direction = ParameterDirection.InputOutput;
            OutPutParhName.Value=hName;

            SqlParameter OutPutParmgrid = new SqlParameter("mgrid", SqlDbType.Int);
            OutPutParmgrid.Direction = ParameterDirection.InputOutput;
            if (mgrid != 0)
            {
                OutPutParmgrid.Value = mgrid;
            }
            else
            {
                OutPutParmgrid.Value = DBNull.Value;
            }

            Sqlcmd1.Parameters.Add(OutPutParCodeID);
            Sqlcmd1.Parameters.Add(OutPutParhName);
            Sqlcmd1.Parameters.Add(OutPutParmgrid);

            try
            {
                SqlConnection1.Open();
                Sqlcmd1.ExecuteNonQuery();                
                InsOrUpddataTable(ref DtCity, OutPutParCodeID);
                return true;
            }
            catch (SqlException e1)
            {
                string ErrMsg = ErrFinder1.GetErrMessage(e1.GetType().Name, e1.ErrorCode.ToString(), e1.Message);
                System.Windows.Forms.MessageBox.Show(ErrMsg, "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                return false;
            }
            finally
            {
                SqlConnection1.Close();
            }

        }                

        static public void DeleteData(int CodeID)
        {
            GetConnectionString Sett = new GetConnectionString();
            SqlConnection SqlConnection1 = new SqlConnection(Sett.ConnectionSrting());

            SqlCommand Sqlcmd1 = new SqlCommand();
            Sqlcmd1.Connection = SqlConnection1;
            Sqlcmd1.CommandType = CommandType.StoredProcedure;
            Sqlcmd1.CommandText = "speDel_dtCity";
            Sqlcmd1.Parameters.Add("CodeID", SqlDbType.Int).Value = CodeID;
            try
            {
                SqlConnection1.Open();
                Sqlcmd1.ExecuteNonQuery();
                DeleteDataInDataTable(ref DtCity, CodeID);
            }
            catch (SqlException e1)
            {
                string ErrMsg = ErrFinder1.GetErrMessage(e1.GetType().Name, e1.Errors[0].Number.ToString(), e1.Message);
                System.Windows.Forms.MessageBox.Show(ErrMsg, "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                
            }
            finally
            {
                SqlConnection1.Close();
            }
        }

        #endregion



        static private object[] GetObjectArray(SqlDataReader SqlDr)
        {
            int i = 0;
            object[] MyObj = new object[SqlDr.FieldCount];
            while (SqlDr.FieldCount>i)
            {
                MyObj[i] = SqlDr[i];
                i+=1;
            }
            return MyObj;
        }

        static private void InsOrUpddataTable(ref DataTable Dt, SqlParameter SqlPar)
        {
            string SelectCommand = "select * from " + Dt.TableName + " where " + SqlPar.ParameterName + "=" + SqlPar.Value.ToString();
            GetConnectionString Sett = new GetConnectionString();
            SqlConnection SqlConnection1 = new SqlConnection(Sett.ConnectionSrting());
            SqlCommand Sqlcmd1 = new SqlCommand(SelectCommand, SqlConnection1);
            SqlDataReader SqlDr;

            try
            {
                SqlConnection1.Open();
                SqlDr = Sqlcmd1.ExecuteReader();
                if (SqlDr.Read())
                {
                    Dt.BeginLoadData();
                    //Finds and updates a specific row. If no matching row is found, a new row is created using the given values
                    Dt.LoadDataRow(GetObjectArray(SqlDr), true);
                    Dt.EndLoadData();
                    Dt.AcceptChanges();
                }
            }
            catch (SqlException e1)
            {
                string ErrMsg = ErrFinder1.GetErrMessage(e1.GetType().Name, e1.ErrorCode.ToString(), e1.Message);
                System.Windows.Forms.MessageBox.Show(ErrMsg, "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
            }
            finally
            {
                SqlConnection1.Close();
            }
        }
        
        static private void DeleteDataInDataTable(ref DataTable Dt,  int ID)
        {
            DataRow dr;
            dr = Dt.Rows.Find(ID);
            Dt.Rows.Remove(dr);
            Dt.AcceptChanges();
        }

    }
}
